<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="support.*, java.util.*, java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
	<title>Graduate School Application - Confirmation</title>
	<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
	<link rel="stylesheet" type="text/css" href="../resource/columnCSS.css" /> 
	<link rel="stylesheet" type="text/css" href="../resource/styleCSS.css" /> 	
</head>
<body>
		<%
		// DEBUG flag
		boolean debug = true; 
		
		// Get Session Information
		String fname = (String)session.getAttribute("fname");
		String lname = (String)session.getAttribute("lname");
		String mname = (String)session.getAttribute("mname");
		String citizen = (String)session.getAttribute("citizen");
		String residence = (String)session.getAttribute("residence");
		String address = (String)session.getAttribute("address"); 
		String city = (String)session.getAttribute("city");
		String state = (String)session.getAttribute("state");
		Integer zipcode = (Integer)session.getAttribute("zipcode");
		Integer areacode = (Integer)session.getAttribute("areacode");
		Integer number = (Integer)session.getAttribute("number");
		Integer countrycode = (Integer)session.getAttribute("countrycode");
		Boolean domestic = (Boolean)session.getAttribute("domestic");
		Vector degrees = (Vector) session.getAttribute("degrees");
		String specialization = (String)session.getAttribute("specialization");
	
		// JH: degree fields
		String degree_loc;
		String degree_univ;
		String degree_discip;
		String degree_month;
		String degree_year;
		String degree_gpa;
		String degree_title;
		String degree_trans;
		
		int degree_month_int;
		int degree_year_int;
		double degree_gpa_double;
		
		// JH: id fields for student
		int citizen_id = 0;
		int residence_id = 0;
		int spec_id = 0;
		
		// JH: id fields for degrees
		int degree_univ_loc_id = 0;
		int degree_univ_id = 0;
		int degree_discip_id = 0;
		int degree_student_id = 0;
		
		// JH: flag for inserting student
		boolean isNull = false;
		
		if ( fname == null || lname == null || citizen == null && 
				residence == null && address == null || city == null && 
				zipcode == null || areacode == null || number == null && 
				domestic == null || specialization == null) {
			isNull = true;
			System.err.println("[ERROR] Session Info is NULL: " + specialization);
		}
	%>
	<%-- Open Connection Code--%>
	<% 
		String db_url = "jdbc:postgresql://localhost:5432/CSE135_SP11";
		String user ="postgres";
		String passwd ="postgres";
		Connection conn;
		PreparedStatement pstmt;
		ResultSet rset;
					
		try {
			Class.forName("org.postgresql.Driver");	
		}
		catch (java.lang.ClassNotFoundException e){
			System.err.println("ClassNotFoundException: " + e.getMessage());
		}
					
		try {
			conn = DriverManager.getConnection(db_url, user, passwd);
			
			conn.setAutoCommit(false);
	%>
	
	<p class="sansserif">
	<div class="container">
	<div class="center_div">
		<div class="colmask threecol">
		<div class="colmid">
		<div class="colleft">
			<div class="bodycol">
			<p><b>Confirmation</b> </p>
			<br>
			<ul class="none">
			<%-- Student: Look Up Code --%>
			<%
				if (!isNull) {
					
					// JH: Get ID for citizenship
					pstmt = conn.prepareStatement(
						"SELECT id " + 
						"FROM Countries " + 
						"WHERE country_name = ?"); 
					
					pstmt.setString(1, citizen);
								
					rset = pstmt.executeQuery();
					
					while (	rset.next() ) {
						citizen_id = rset.getInt("id");
					}
					
					if (debug) {
						System.err.println("[DEBUG] Citizenship: " + citizen + 
								"(" + citizen_id + ")");
					}
		
					// JH: Get ID for residence
					pstmt.setString(1, residence);
					
					rset = pstmt.executeQuery();
					
					while ( rset.next() ) {
						residence_id = rset.getInt("id");	
					}
					
					if (debug) {
						System.err.println("[DEBUG] Residence ID: " + residence + 
								"(" + residence_id + ")");
					}
					
					// JH: Get ID for specialization
					pstmt = conn.prepareStatement(
							"SELECT id " + 
							"FROM Specializations " + 
							"WHERE specialization = ?"); 
						
					pstmt.setString(1, specialization);
									
					rset = pstmt.executeQuery();
						
					while (	rset.next() ) {
						spec_id = rset.getInt("id");
					}
						
					if (debug) {
						System.err.println("[DEBUG] Specialization: " + specialization + 
								"(" + spec_id + ")");
					}
				}
			%>
			<%-- Student: Insertion Code --%>
			<%
				// INSERT to Students table
				if (!isNull) {
					
					int expected_param = 11;
					String insert_stmt = "INSERT INTO Students (first_name, last_name, " + 
					"citizenship, residence, street_addr, city, zip_code, area_code, " +
					"telephone, residency, specialization";
					
					int mname_val = 0;
					int state_val = 0;
					int countrycode_val = 0;
					
					if (mname != null) {
						insert_stmt += ", middle_name";
						mname_val = ++expected_param;
					}
					
					if (state != null) {
						insert_stmt += ", state";
						state_val = ++expected_param;
					}
					
					if (countrycode != null) {
						insert_stmt += ", country_code";
						countrycode_val = ++expected_param;
					}
					
					insert_stmt += ") VALUES (";
					
					for (int count = 0; count < expected_param; count++) {
						if (count > 0)
							insert_stmt += ",";
						
						insert_stmt += "?";
					}
					insert_stmt += ")";
						
					pstmt = conn.prepareStatement(insert_stmt);
					
					pstmt.setString(1, fname);
					pstmt.setString(2, lname);
					pstmt.setInt(3, citizen_id);
					pstmt.setInt(4, residence_id);
					pstmt.setString(5, address);
					pstmt.setString(6, city);
					pstmt.setInt(7, zipcode);
					pstmt.setInt(8, areacode);
					pstmt.setInt(9, number);
					pstmt.setBoolean(10, domestic);
					pstmt.setInt(11, spec_id);
				
					if (mname != null) {
						pstmt.setString(mname_val, mname);
					}
					
					if (state != null) {
						pstmt.setString(state_val, state);
					}
					
					if (countrycode != null) {
						pstmt.setInt(countrycode_val, countrycode);
					}
					
					pstmt.executeUpdate();
					out.println("<li>Applicant has now been entered into the database</li>");
					
					// JH: Store ID for student
					if (mname != null) {
						pstmt = conn.prepareStatement(
								"SELECT id " + 
								"FROM Students " + 
								"WHERE first_name = ? AND last_name = ? " +
								"AND middle_name = ?");
						pstmt.setString(1, fname);
						pstmt.setString(2, lname);
						pstmt.setString(3, mname);
					}
					else {
						pstmt = conn.prepareStatement(
							"SELECT id " + 
							"FROM Students " + 
							"WHERE first_name = ? AND last_name = ?"); 
					
						pstmt.setString(1, fname);
						pstmt.setString(2, lname);
					}
					
					rset = pstmt.executeQuery();
					
					while (	rset.next() ) {
						degree_student_id = rset.getInt("id");
					}
					
					if (debug) {
						if ( mname != null ) {
							System.err.println("[DEBUG] Student: " + fname + " " + 
									mname + lname + "(" + degree_student_id + ")");
						}
						else {
							System.err.println("[DEBUG] Student: " + fname + " " + 
								lname + "(" + degree_student_id + ")");
						}
					}
				}
			%>
			<%-- Degrees: Look Up Code --%>
			<%				
				// JH: Loop through all degrees
				if (!isNull && degrees != null) {
					for(int count = 0; count < degrees.size(); count++) {
						Vector currDegree = (Vector) degrees.elementAt(count);
						
						degree_loc = (String)currDegree.elementAt(0);
						degree_univ = (String)currDegree.elementAt(1);
						degree_discip = (String)currDegree.elementAt(2);
						degree_month = (String)currDegree.elementAt(3);
						degree_year = (String)currDegree.elementAt(4);
						degree_gpa = (String)currDegree.elementAt(5);
						degree_title = (String)currDegree.elementAt(6);
						degree_trans = (String)currDegree.elementAt(7);
						
						if (debug) {
							System.err.println("[DEBUG] degree location: " + degree_loc);
							System.err.println("[DEBUG] degree university: " + degree_univ);
							System.err.println("[DEBUG] degree discipline: " + degree_discip);
							System.err.println("[DEBUG] degree month: " + degree_month);
							System.err.println("[DEBUG] degree year: " + degree_year);
							System.err.println("[DEBUG] degree gpa: " + degree_gpa);
							System.err.println("[DEBUG] degree title: " + degree_title);
							System.err.println("[DEBUG] degree trans: " + degree_trans);
						}
						
						try {
							degree_month_int = Integer.parseInt(degree_month);
						}
						catch (NumberFormatException e){
							System.err.println("NumberFormatException(degree_month_int): " + e);
							degree_month_int = 0;
						}
						
						try {
							degree_year_int = Integer.parseInt(degree_year);
						}
						catch (NumberFormatException e){
							System.err.println("NumberFormatException(degree_year_int): " + e);
							degree_year_int = 0;
						}
						
						try {
							degree_gpa_double = Double.parseDouble(degree_gpa);
						}
						catch (NumberFormatException e){
							System.err.println("NumberFormatException(degree_gpa_float): " + e);
							degree_gpa_double = 3.14;
						}
						
						
						// JH: Get ID for university
						pstmt = conn.prepareStatement(
							"SELECT id " + 
							"FROM Universities " + 
							"WHERE university = ?"); 
						
						pstmt.setString(1, degree_univ);
									
						rset = pstmt.executeQuery();
						
						while (	rset.next() ) {
							degree_univ_id = rset.getInt("id");
						}
						
						if (debug) {
							System.err.println("[DEBUG] University of Degree: " + degree_univ + 
									"(" + degree_univ_id + ")");
						}
						
						// JH: add user entered university to university table
						if ( degree_univ_id == 0 ) {
								// JH: look up university location
								pstmt = conn.prepareStatement(
									"SELECT id " + 
									"FROM Locations " + 
									"WHERE location = ?"); 
								
								pstmt.setString(1, degree_loc);
											
								rset = pstmt.executeQuery();
								
								while (	rset.next() ) {
									degree_univ_loc_id = rset.getInt("id");
								}
								
								if (debug) {
									System.err.println("[DEBUG] Location of University: " + degree_loc + 
											"(" + degree_univ_loc_id + ")");
								}
								
								// JH: Insert new university to universities table
								pstmt = conn.prepareStatement(
									"INSERT INTO Universities (location, university) " + 
									"VALUES (?, ?)");; 
								
								pstmt.setInt(1, degree_univ_loc_id);
								pstmt.setString(2, degree_univ);
								
								pstmt.executeUpdate();
								
								// JH: Look up new university id
								pstmt = conn.prepareStatement(
									"SELECT id " + 
									"FROM Universities " + 
									"WHERE university = ?"); 
						
								pstmt.setString(1, degree_univ);
						
								rset = pstmt.executeQuery();
								
								while (	rset.next() ) {
									degree_univ_id = rset.getInt("id");
								}
								
								if (debug) {
									System.err.println("[DEBUG] University of Degree: " + degree_univ + 
											"(" + degree_univ_id + ")");
								}
							
						}
						// JH: Get ID for discipline
						pstmt = conn.prepareStatement(
							"SELECT id " + 
							"FROM Disciplines " + 
							"WHERE discipline = ?"); 
						
						pstmt.setString(1, degree_discip);
									
						rset = pstmt.executeQuery();
						
						while (	rset.next() ) {
							degree_discip_id = rset.getInt("id");
						}
						
						if (debug) {
							System.err.println("[DEBUG] Discipline for Degree: " + degree_discip + 
									"(" + degree_discip_id + ")");
						}
						
						// JH: add user entered discipline to university table
						if ( degree_discip_id == 0 ) {
								
								// JH: Insert new discipline to Discipines table
								pstmt = conn.prepareStatement(
									"INSERT INTO Disciplines (discipline) " + 
									"VALUES (?)");; 
								
								pstmt.setString(1, degree_discip);
								
								pstmt.executeUpdate();
								
								// JH: Look up new ID for discipline
								pstmt = conn.prepareStatement(
									"SELECT id " + 
									"FROM Disciplines " + 
									"WHERE discipline = ?"); 
						
								pstmt.setString(1, degree_discip);
									
								rset = pstmt.executeQuery();
						
								while (	rset.next() ) {
									degree_discip_id = rset.getInt("id");
								}
						
								if (debug) {
									System.err.println("[DEBUG] Discipline for Degree: " + degree_discip + 
									"(" + degree_discip_id + ")");
								}
							
						}
			%>
			<%-- Degrees: Insertion Code --%>
			<%
						// JH: Insert degree to Degrees
						/*
						pstmt = conn.prepareStatement(
						"INSERT INTO Degrees (location, university, discipline, " + 
						"grad_month, grad_year, gpa, title, transcript, student)" + 
						"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");*/
						pstmt = conn.prepareStatement(
								"INSERT INTO Degrees (university, discipline, " + 
								"grad_month, grad_year, gpa, title, transcript, student)" + 
								"VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
						
						//pstmt.setString(1, degree_loc);
						pstmt.setInt(1, degree_univ_id);
						pstmt.setInt(2, degree_discip_id);
						pstmt.setInt(3, degree_month_int);
						pstmt.setInt(4, degree_year_int);
						pstmt.setDouble(5, degree_gpa_double);
						pstmt.setString(6, degree_title);
						pstmt.setString(7, degree_trans);
						pstmt.setInt(8, degree_student_id);
						
						pstmt.executeUpdate();
					}
					
					out.println("<p>with " + degrees.size() + " degrees</p>");
				}	
			
				conn.commit();
				conn.setAutoCommit(true);
			%>
			<%
			}
			catch(SQLException e){
				out.println("<li>Unable to add student to database</li>");
				System.err.println("SQLException: " + e.getMessage());
			}
			%>	
			</ul>
			</div>
			<div class="sidecol">
				<p><a href="../invalidate.jsp">Logout</a></p>
			</div>
		</div>
		</div>
		</div>
	<div style="clear:both;"></div>
	</div>
	</div>
</body>
</html>